﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.Configuration;

namespace Web.Framework.Helpers
{
    public class ExcelHelperBase
    {
        public static DataTable GetDataTableFromExcel(string filePath)
        {
            //<add name="xls" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Sample1.xls;Extended Properties=Excel 8.0"/>
            //<add name="xlsx" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Sample.xlsx;Extended Properties=Excel 12.0"/>            
            // string connectionString = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0; Data Source={0};Extended Properties=""Excel 12.0;""", filePath);
            string connectionString = string.Format(ConfigurationManager.ConnectionStrings["ExcelProvider"].ConnectionString, filePath);

            OleDbConnection objConnection = new OleDbConnection();

            try
            {
                objConnection = new OleDbConnection(connectionString);

                objConnection.Open();

                DataTable dtSchema = objConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                string sheetName = dtSchema.Rows[0]["TABLE_NAME"].ToString();

                var adapter = new OleDbDataAdapter("SELECT * FROM [" + sheetName + "]", objConnection);
                var ds = new DataSet();

                adapter.Fill(ds, "anyNameHere");

                return ds.Tables["anyNameHere"];
            }
            finally
            {
                objConnection.Close();
            }
        }

        public static bool CheckIfSheetNameExists(string sheetName, DataTable dtSchema)
        {
            foreach (DataRow dataRow in dtSchema.Rows)
            {
                if (sheetName == dataRow["TABLE_NAME"].ToString())
                {
                    return true;
                }
            }
            return false;
        }
    }
}
